using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class DocLinkDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procDocLink_add]";
        private string _strSPUpdateName = "dbo.[procDocLink_update]";
        private string _strSPDeleteName = "dbo.[procDocLink_delete]";
        private string _strSPGetName = "dbo.[procDocLink_get]";
        private string _strSPGetAllName = "dbo.[procDocLink_getall]";
        private string _strSPGetPages = "dbo.[procDocLink_getpaged]";
        private string _strSPIsExist = "dbo.[procDocLink_isexist]";
        private string _strTableName = "[DocLink]";
        private string _strSPGetTransferOutName = "dbo.[procDocLink_gettransferout]";
        #endregion Local Variable

        #region Method
        public DocLinkInfo Get(
        Int32 ID,
        ref string sErr)
        {
            DocLinkInfo objEntr = new DocLinkInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter(DocLinkInfo.Field.ID.ToString(), ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = (DocLinkInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            DocLinkInfo result = new DocLinkInfo();
            result.ID = (dt.Rows[i][DocLinkInfo.Field.ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][DocLinkInfo.Field.ID.ToString()]));
            result.DB = (dt.Rows[i][DocLinkInfo.Field.DB.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.DB.ToString()]));
            result.TRANS_TYPE = (dt.Rows[i][DocLinkInfo.Field.TRANS_TYPE.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.TRANS_TYPE.ToString()]));
            result.REFERENCE = (dt.Rows[i][DocLinkInfo.Field.REFERENCE.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.REFERENCE.ToString()]));
            result.URL = (dt.Rows[i][DocLinkInfo.Field.URL.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.URL.ToString()]));
            result.TITLE = (dt.Rows[i][DocLinkInfo.Field.TITLE.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.TITLE.ToString()]));
            result.CATEGORY = (dt.Rows[i][DocLinkInfo.Field.CATEGORY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.CATEGORY.ToString()]));
            result.SUBJECT = (dt.Rows[i][DocLinkInfo.Field.SUBJECT.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.SUBJECT.ToString()]));
            result.AUTHOR = (dt.Rows[i][DocLinkInfo.Field.AUTHOR.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.AUTHOR.ToString()]));
            result.KEYWORDS = (dt.Rows[i][DocLinkInfo.Field.KEYWORDS.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.KEYWORDS.ToString()]));
            result.COMMENTS = (dt.Rows[i][DocLinkInfo.Field.COMMENTS.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DocLinkInfo.Field.COMMENTS.ToString()]));

            return result;
        }

        public DataTable GetAll(ref string sErr, string db, string transtype = null, string refer = null)
        {
            InitConnect();
            string sql = @"SELECT     ID, DB, TRANS_TYPE, REFERENCE, URL, TITLE, CATEGORY, SUBJECT, AUTHOR, KEYWORDS, COMMENTS
                FROM         DocLink
                WHERE DB = @DB ";
            if (transtype != null) sql += " AND TRANS_TYPE = @TRANS_TYPE ";
            if (refer != null) sql += " AND REFERENCE = @REFERENCE ";
            InitCommand(sql, CommandType.Text);
            if (transtype != null) AddParameter("DB", db);
            if (transtype != null) AddParameter("TRANS_TYPE", transtype);
            if (refer != null) AddParameter("REFERENCE", refer);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(DocLinkInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO DocLink( DB, TRANS_TYPE, REFERENCE, URL, TITLE, CATEGORY, SUBJECT, AUTHOR, KEYWORDS, COMMENTS)
                VALUES     (@DB,@TRANS_TYPE,@REFERENCE,@URL,@TITLE,@CATEGORY,@SUBJECT,@AUTHOR,@KEYWORDS,@COMMENTS)
                SELECT SCOPE_IDENTITY()", CommandType.Text);
            //AddParameter(DocLinkInfo.Field.ID.ToString(), objEntr.ID);
            AddParameter(DocLinkInfo.Field.DB.ToString(), objEntr.DB);
            AddParameter(DocLinkInfo.Field.TRANS_TYPE.ToString(), objEntr.TRANS_TYPE);
            AddParameter(DocLinkInfo.Field.REFERENCE.ToString(), objEntr.REFERENCE);
            AddParameter(DocLinkInfo.Field.URL.ToString(), objEntr.URL);
            AddParameter(DocLinkInfo.Field.TITLE.ToString(), objEntr.TITLE);
            AddParameter(DocLinkInfo.Field.CATEGORY.ToString(), objEntr.CATEGORY);
            AddParameter(DocLinkInfo.Field.SUBJECT.ToString(), objEntr.SUBJECT);
            AddParameter(DocLinkInfo.Field.AUTHOR.ToString(), objEntr.AUTHOR);
            AddParameter(DocLinkInfo.Field.KEYWORDS.ToString(), objEntr.KEYWORDS);
            AddParameter(DocLinkInfo.Field.COMMENTS.ToString(), objEntr.COMMENTS);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(DocLinkInfo objEntr)
        {
            openConnection();
            InitSPCommand(_strSPUpdateName);
            AddParameter(DocLinkInfo.Field.ID.ToString(), objEntr.ID);
            AddParameter(DocLinkInfo.Field.DB.ToString(), objEntr.DB);
            AddParameter(DocLinkInfo.Field.TRANS_TYPE.ToString(), objEntr.TRANS_TYPE);
            AddParameter(DocLinkInfo.Field.REFERENCE.ToString(), objEntr.REFERENCE);
            AddParameter(DocLinkInfo.Field.URL.ToString(), objEntr.URL);
            AddParameter(DocLinkInfo.Field.TITLE.ToString(), objEntr.TITLE);
            AddParameter(DocLinkInfo.Field.CATEGORY.ToString(), objEntr.CATEGORY);
            AddParameter(DocLinkInfo.Field.SUBJECT.ToString(), objEntr.SUBJECT);
            AddParameter(DocLinkInfo.Field.AUTHOR.ToString(), objEntr.AUTHOR);
            AddParameter(DocLinkInfo.Field.KEYWORDS.ToString(), objEntr.KEYWORDS);
            AddParameter(DocLinkInfo.Field.COMMENTS.ToString(), objEntr.COMMENTS);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        Int32 ID
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter(DocLinkInfo.Field.ID.ToString(), ID);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

      
        public Boolean IsExist(
        Int32 ID
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);
            AddParameter(DocLinkInfo.Field.ID.ToString(), ID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(DocLinkInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public string Delete(string db, string transtype, string refer)
        {
            openConnection();
            InitCommand(@"DELETE FROM DocLink
                WHERE DB = @DB AND TRANS_TYPE = @TRANS_TYPE AND REFERENCE = @REFERENCE ", CommandType.Text);
            AddParameter(DocLinkInfo.Field.DB.ToString(), db);
            AddParameter(DocLinkInfo.Field.TRANS_TYPE.ToString(), transtype);
            AddParameter(DocLinkInfo.Field.REFERENCE.ToString(), refer);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
    }
}
